# -*- coding: utf-8 -*-

# import datetime
import os
import sqlite3
import xlrd

from app import app, db, lm

from flask import abort, g, render_template, flash, redirect, url_for
from flask import request, send_from_directory

from flask_login import current_user, login_user, logout_user, login_required

from flask_wtf import FlaskForm

from wtforms import BooleanField, FileField, PasswordField
from wtforms import StringField, SubmitField

from wtforms.validators import DataRequired, EqualTo
from wtforms.validators import ValidationError

from werkzeug import secure_filename

from app.models import (User, Account, Summary, TradingRecord,
                        Holding, PairTrading, DetailAnalysis)

# from .get_excel_files import is_validated

ALLOWED_EXTENSIONS = app.config['ALLOWED_EXTENSIONS']
basedir = app.config['BASEDIR']


class LoginForm(FlaskForm):
    '''用户登陆表格'''
    username = StringField('用户名：', validators=[DataRequired()])
    password = PasswordField('密码：', validators=[DataRequired()])
    remember_me = BooleanField('记住我')
    submit = SubmitField('登入')


class RegisterForm(FlaskForm):
    '''用户注册表格'''
    username = StringField('用户名：', validators=[DataRequired()])
    password = PasswordField('新密码：', validators=[DataRequired(),
                             EqualTo('password2',
                                     message='两次密码输入必须一致')])
    password2 = PasswordField('重复密码', validators=[DataRequired()])
    submit = SubmitField('注册')

    def validate_username(self, field):
        if User.query.filter_by(username=field.data).first():
            raise ValidationError('用户名已存在')


class AccountForm(FlaskForm):
    '''用户注册表格'''
    name = StringField('账户名(邮箱)：', validators=[DataRequired()])
    show_name = StringField('显示名称：', validators=[DataRequired()])
    submit = SubmitField('新增')


class FileUploadForm(FlaskForm):
    '''上传交易记录表格'''
    upload_file = FileField(
        '请上传从页面导出的交易记录文件(xls或者xlsx后缀)',
        validators=[DataRequired()])
    submit = SubmitField('上传')


@lm.user_loader
def load_user(id):
    return User.query.get(int(id))

# @app.before_request
# def before_request():
    # g.user = current_user


@app.route('/login', methods=['POST', 'GET'])
def login():
    form = LoginForm()
    if form.validate_on_submit():
        user = User.query.filter_by(username=form.username.data).first()
        if user is not None and user.verify_password(form.password.data):
            login_user(user, form.remember_me.data)
            return redirect(url_for('index'))
            flash('登录成功！', 'success')
            return render_template('index.html')
        else:
            flash('用户名或密码错误，请重新输入。', 'danger')
    return render_template('login.html', form=form)


@app.route('/logout')
def logout():
    logout_user()
    return redirect(url_for('login'))


@app.route('/', methods=['POST', 'GET'])
@login_required
def index():
    form = AccountForm()
    accounts = []
    count = 0
    if request.method == 'POST':
        if form.validate_on_submit():
            name = form.name.data
            show_name = form.show_name.data
            account = Account.query.filter_by(
                name=name,
                user=current_user).all()
            if account:
                print('账户已存在！')
                flash('账户已存在！', 'warning')
            else:
                account = Account(name, show_name, current_user)
                db.session.add(account)
                flash('新增账户成功', 'success')
            db.session.commit()
    accounts = current_user.accounts.all()
    count = len(accounts)
    print(count)
    return render_template('index.html', form=form,
                           accounts=accounts, count=count)


@app.route('/uploads', methods=['POST', 'GET'])
@login_required
def uploads():
    print("ok!")
    form = FileUploadForm()
    filename = ''
    username = current_user.username
    file_path = os.path.join(app.config['UPLOAD_FOLDER'], username)
    if request.method == 'POST':
        if form.validate_on_submit():
            file = request.files['upload_file']
            print(file)
            if file and allowed_file(file.filename):
                filename = secure_filename(file.filename)
                if not os.path.exists(file_path):
                    os.mkdir(file_path)
                fullfilename = os.path.join(file_path, filename)
                # filelist = os.listdir(file_path)
                print("file_path=%s" % fullfilename)
                file.save(fullfilename)
                flash('文件上传成功。', 'success')
        else:
            flash('文件上传失败！', 'danger')
    if os.path.exists(file_path):
        attr_list = []
        filelist = os.listdir(file_path)
        count = len(filelist)
        for item in filelist:
            check = is_validated(username, item)
            attr_list.append(check)
            fullfilename = os.path.join(file_path, item)
            print('fullfilename=%s' % fullfilename)
            # if check:
            #     get_file_content(username, fullfilename)
        # return render_template(
        #     'upload_files.html',
        #     filelist=filelist,
        #     attr_list=attr_list,
        #     count=len(filelist))
    else:
        print('目录不存在！')
        abort(404)
    return render_template('uploads.html', form=form, count=count,
                           filelist=filelist, attr_list=attr_list)


# @app.route('/uploads/<username>')
# @login_required
# def uploaded_file(username):
#     if current_user.username == username:
#         file_path = os.path.join(
#             app.config['UPLOAD_FOLDER'],
#             current_user.username)
#         if os.path.exists(file_path):
#             attr_list = []
#             filelist = os.listdir(file_path)
#             for item in filelist:
#                 check = is_validated(username, item)
#                 attr_list.append(check)
#                 fullfilename = os.path.join(file_path, item)
#                 print('fullfilename=%s' % fullfilename)
#                 if check:
#                     get_file_content(username, fullfilename)
#             return render_template(
#                 'upload_files.html',
#                 filelist=filelist,
#                 attr_list=attr_list,
#                 count=len(filelist))
#         else:
#             print('目录不存在！')
#             abort(404)
#     else:
#         abort(404)


# @app.route('/uploads/<username>/<filename>')
# @login_required
# def download_uploaded_file(username, filename):
#     if current_user.username == username:
#         file_path = os.path.join(
#             app.config['UPLOAD_FOLDER'],
#             current_user.username,
#             filename)
#         if os.path.exists(file_path):
#             return send_from_directory(
#                 app.config['UPLOAD_FOLDER'],
#                 username, filename)
#         else:
#             print('文件不存在！')
#             abort(404)
#     else:
#         abort(404)


@app.route('/detail/<account_num>/<year>/<month>')
@login_required
def show_result_detail(account_num, year, month):
    conn = sqlite3.connect("app/data.sqlite")
    table_name = "result_detail_%s_%s_%s" % (account_num, year, month)
    sql = "SELECT * FROM %s" % table_name
    details = conn.execute(sql).fetchall()
    table_name = "pairinglist_%s_%s_%s" % (account_num, year, month)
    sql = "SELECT * FROM %s" % table_name
    trading_list = conn.execute(sql).fetchall()
    conn.close()
    result_list = []
    for i in range(len(details)):
        result_list.append(trading_list[i] + details[i])
    return render_template('showdetail.html', results=result_list,
                           account_num=account_num, year=year,
                           month=month, length=len(details))


@app.route('/history')
@login_required
def history():
    return render_template('history.html')


@app.route('/about')
@login_required
def about():
    return render_template('about.html')


@app.route('/records')
@login_required
def records():
    account = current_user.accounts.first()
    records = account.trading_record.all()
    count = len(records)
    return render_template(
        'records.html',
        count=count,
        records=records)


@app.route('/register', methods=['POST', 'GET'])
def register():
    form = RegisterForm()
    if form.validate_on_submit():
        user = User.query.filter_by(username=form.username.data).first()
        if user is not None:
            flash('用户已存在，请重新选择用户名。', 'danger')
            return render_template('register.html', form=form)
        else:
            new_user = User(form.username.data, form.password.data)
            new_user.authenticated = True
            db.session.add(new_user)
            db.session.commit()
            login_user(new_user)
            return redirect(url_for('index'))
    return render_template('register.html', form=form)


#########################自定义函数#########################
def allowed_file(filename):
    return ('.' in filename and
            filename.rsplit('.', 1)[1] in ALLOWED_EXTENSIONS)


def is_validated(username, filename):
    is_ok = False
    fullfilename = os.path.join(
        app.config['UPLOAD_FOLDER'],
        username,
        filename)
    print('正在验证Excel文件：%s...' % fullfilename)
    excel_workbook = xlrd.open_workbook(fullfilename)
    excel_sheet = excel_workbook.sheets()[0]
    print('当前表格名称：%s' % excel_sheet.name)
    if (excel_sheet.cell(0, 0).value == '时间' and
       excel_sheet.cell(0, 1).value == '市场' and
       excel_sheet.cell(0, 2).value == '类型' and
       excel_sheet.cell(0, 3).value == '价格' and
       excel_sheet.cell(0, 4).value == '数量' and
       excel_sheet.cell(0, 5).value == '成交额 ' and
       excel_sheet.cell(0, 6).value == '手续费' and
       excel_sheet.cell(0, 7).value == '手续费结算币种'):
        is_ok = True
        print('Excel文件 %s 符合格式。' % fullfilename)
    else:
        print('Excel文件 %s 不符合格式！' % fullfilename)
    return is_ok


#检查过文件存在、格式符合才调用该函数
def get_file_content(username, filename):
    fullfilename = os.path.join(basedir, '../upload_files', username, filename)
    print('正在处理Excel文件：%s...' % fullfilename)
    excel_workbook = xlrd.open_workbook(fullfilename)
    excel_sheet = excel_workbook.sheets()[0]
    user = User.query.filter_by(username=username).first()
    account = user.accounts.first()
    for row in range(1, excel_sheet.nrows):
        cell_content = excel_sheet.cell(row, 2).value
        if cell_content:
            if cell_content == '买':
                buy = 1
            elif cell_content == '卖':
                buy = -1
            else:
                print('记录出错？买卖项为其他内容：%s' % cell_content)
                buy = 0
            price = float(excel_sheet.cell(row, 3).value)
            amount = float(excel_sheet.cell(row, 4).value)
            trans = float(excel_sheet.cell(row, 5).value)
            fee = float(excel_sheet.cell(row, 6).value)
            # print(price, amount, trans, fee)
            # print(type(price), type(amount), type(trans), type(fee))
            record = TradingRecord.query.filter(
                TradingRecord.datetime == excel_sheet.cell(row, 0).value,
                TradingRecord.contract == excel_sheet.cell(row, 1).value,
                TradingRecord.buy == buy,
                TradingRecord.price == price,
                TradingRecord.amount == amount,
                TradingRecord.trans == trans,
                TradingRecord.fee == fee,
                TradingRecord.fee_type == excel_sheet.cell(row, 7).value
                ).first()
            if record:
                print('记录已存在，不进行更新：%s' % record)
            else:
                record = TradingRecord(
                    excel_sheet.cell(row, 0).value,
                    excel_sheet.cell(row, 1).value,
                    buy,
                    float(excel_sheet.cell(row, 3).value),
                    float(excel_sheet.cell(row, 4).value),
                    float(excel_sheet.cell(row, 5).value),
                    float(excel_sheet.cell(row, 6).value),
                    excel_sheet.cell(row, 7).value,
                    account)
                db.session.add(record)
            db.session.commit()
